namespace ASPNET.StarterKit.IssueTracker.DataAccessLayer
{
    using ASPNET.StarterKit.IssueTracker.BusinessLogicLayer;
    using Microsoft.VisualBasic;
    using Microsoft.VisualBasic.CompilerServices;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.CompilerServices;
    using System.Text;

    public class SQLDataAccessLayer : DataAccessLayerBaseClass
    {
        private const string SP_CATEGORY_CREATE = "IssueTracker_ProjectCategories_CreateNewCategory";
        private const string SP_CATEGORY_DELETE = "IssueTracker_ProjectCategories_DeleteCategory";
        private const string SP_CATEGORY_GETCATEGORIESBYPROJECTID = "IssueTracker_ProjectCategories_GetCategoriesByProjectId";
        private const string SP_CUSTOMFIELD_CREATE = "IssueTracker_CustomField_CreateNewCustomField";
        private const string SP_CUSTOMFIELD_DELETE = "IssueTracker_CustomField_DeleteCustomField";
        private const string SP_CUSTOMFIELD_GETCUSTOMFIELDSBYISSUEID = "IssueTracker_CustomField_GetCustomFieldsByIssueId";
        private const string SP_CUSTOMFIELD_GETCUSTOMFIELDSBYISSUEID_2 = "IssueTracker_CustomField_GetCustomFieldsByIssueId_2";
        private const string SP_CUSTOMFIELD_GETCUSTOMFIELDSBYPROJECTID = "IssueTracker_CustomField_GetCustomFieldsByProjectId";
        private const string SP_CUSTOMFIELD_SAVECUSTOMFIELDVALUE = "IssueTracker_CustomField_SaveCustomFieldValue";
        private const string SP_CUSTOMFIELD_SAVESingleCUSTOMFIELDVALUE = "IssueTracker_CustomField_SaveCustomFieldValue";
        private const string SP_CUSTOMFIELD_UPDATE = "IssueTracker_CustomField_UpdateCustomField";
        private const string SP_ISSUE_CREATE = "IssueTracker_Issue_CreateNewIssue";
        private const string SP_ISSUE_DELETE = "IssueTracker_Issue_DeleteIssue";
        private const string SP_ISSUE_GETISSUEBYID = "IssueTracker_Issue_GetIssueById";
        private const string SP_ISSUE_GETISSUESBYASSIGNEDUSERNAME = "IssueTracker_Issue_GetIssuesByAssignedUsername";
        private const string SP_ISSUE_GETISSUESBYCREATORUSERNAME = "IssueTracker_Issue_GetIssuesByCreatorUsername";
        private const string SP_ISSUE_GETISSUESBYOWNERUSERNAME = "IssueTracker_Issue_GetIssuesByOwnerUsername";
        private const string SP_ISSUE_GETISSUESBYPROJECTID = "IssueTracker_Issue_GetIssuesByProjectId";
        private const string SP_ISSUE_GETISSUESBYRELEVANCY = "IssueTracker_Issue_GetIssuesByRelevancy";
        private const string SP_ISSUE_UPDATE = "IssueTracker_Issue_UpdateIssue";
        private const string SP_ISSUEATTACHMENT_CREATE = "IssueTracker_IssueAttachment_CreateNewIssueAttachment";
        private const string SP_ISSUEATTACHMENT_DELETE = "IssueTracker_IssueAttachment_Delete";
        private const string SP_ISSUEATTACHMENT_GETISSUEATTACHMENTBYID = "IssueTracker_IssueAttachment_GetIssueAttachmentById";
        private const string SP_ISSUEATTACHMENT_GETISSUEATTACHMENTSBYISSUEID = "IssueTracker_IssueAttachment_GetIssueAttachmentsByIssueId";
        private const string SP_ISSUECOMMENT_CREATE = "IssueTracker_IssueComment_CreateNewIssueComment";
        private const string SP_ISSUECOMMENT_GETISSUECOMMENTBYISSUEID = "IssueTracker_IssueComment_GetIssueCommentsByIssueId";
        private const string SP_ISSUEHISTORY_GETISSUEHISTORYBYISSUEID = "IssueTracker_IssueHistory_GetIssueHistoryByIssueId";
        private const string SP_ISSUENOTIFICATION_CREATE = "IssueTracker_IssueNotification_CreateNewIssueNotification";
        private const string SP_ISSUENOTIFICATION_DELETE = "IssueTracker_IssueNotification_DeleteIssueNotification";
        private const string SP_ISSUENOTIFICATION_GETISSUENOTIFICATIONSBYISSUEID = "IssueTracker_IssueNotification_GetIssueNotificationsByIssueId";
        private const string SP_MILESTONE_CREATE = "IssueTracker_ProjectMilestones_CreateNewMilestone";
        private const string SP_MILESTONE_DELETE = "IssueTracker_ProjectMilestones_DeleteMilestone";
        private const string SP_MILESTONE_GETMILESTONEBYPROJECTID = "IssueTracker_ProjectMilestones_GetMilestonesByProjectId";
        private const string SP_PRIORITY_CREATE = "IssueTracker_ProjectPriorities_CreateNewPriority";
        private const string SP_PRIORITY_DELETE = "IssueTracker_ProjectPriorities_DeletePriority";
        private const string SP_PRIORITY_GETPRIORITIESBYPROJECTID = "IssueTracker_ProjectPriorities_GetPrioritiesByProjectId";
        private const string SP_PROJECT_ADDUSERTOPROJECT = "IssueTracker_Project_AddUserToProject";
        private const string SP_PROJECT_CLONEPROJECT = "IssueTracker_Project_CloneProject";
        private const string SP_PROJECT_CREATE = "IssueTracker_Project_CreateNewProject";
        private const string SP_PROJECT_DELETE = "IssueTracker_Project_DeleteProject";
        private const string SP_PROJECT_GETALLPROJECTS = "IssueTracker_Project_GetAllProjects";
        private const string SP_PROJECT_GETAPROJECTBYID = "IssueTracker_Project_GetProjectById";
        private const string SP_PROJECT_GETPROJECTBYMEMBERUSERNAME = "IssueTracker_Project_GetProjectByUsername";
        private const string SP_PROJECT_REMOVEUSERFROMPROJECT = "IssueTracker_Project_RemoveUserFromProject";
        private const string SP_PROJECT_UPDATE = "IssueTracker_Project_UpdateProject";
        private const string SP_QUERY_DELETEQUERY = "IssueTracker_Query_DeleteQuery";
        private const string SP_QUERY_GETQUERIESBYUSERNAME = "IssueTracker_Query_GetQueriesByUsername";
        private const string SP_QUERY_GETSAVEDQUERY = "IssueTracker_Query_GetSavedQuery";
        private const string SP_QUERY_SAVEQUERY = "IssueTracker_Query_SaveQuery";
        private const string SP_QUERY_SAVEQUERYCLAUSE = "IssueTracker_Query_SaveQueryClause";
        private const string SP_QUERY_GETISSUEHISTORYBYHISTORYID = "IssueTracker_IssueHistory_GetHistoryValueByHistoryId";
        private const string SP_RELATEDISSUE_CREATENEWCHILDISSUE = "IssueTracker_RelatedIssue_CreateNewChildIssue";
        private const string SP_RELATEDISSUE_CREATENEWPARENTISSUE = "IssueTracker_RelatedIssue_CreateNewParentIssue";
        private const string SP_RELATEDISSUE_CREATENEWRELATEDISSUE = "IssueTracker_RelatedIssue_CreateNewRelatedIssue";
        private const string SP_RELATEDISSUE_DELETECHILDISSUE = "IssueTracker_RelatedIssue_DeleteChildIssue";
        private const string SP_RELATEDISSUE_DELETEPARENTISSUE = "IssueTracker_RelatedIssue_DeleteParentIssue";
        private const string SP_RELATEDISSUE_DELETERELATEDISSUE = "IssueTracker_RelatedIssue_DeleteRelatedIssue";
        private const string SP_RELATEDISSUE_GETCHILDISSUES = "IssueTracker_RelatedIssue_GetChildIssues";
        private const string SP_RELATEDISSUE_GETPARENTISSUES = "IssueTracker_RelatedIssue_GetParentIssues";
        private const string SP_RELATEDISSUE_GETRELATEDISSUES = "IssueTracker_RelatedIssue_GetRelatedIssues";
        private const string SP_ROLE_GETALLROLES = "IssueTracker_Role_GetAllRoles";
        private const string SP_STATUS_CREATE = "IssueTracker_ProjectStatus_CreateNewStatus";
        private const string SP_STATUS_DELETE = "IssueTracker_ProjectStatus_DeleteStatus";
        private const string SP_STATUS_GETSTATUSBYPROJECTID = "IssueTracker_ProjectStatus_GetStatusByProjectId";
        private const string SP_USER_AUTHENTICATE = "IssueTracker_User_Authenticate";
        private const string SP_USER_CREATENEWUSER = "IssueTracker_User_CreateNewUser";
        private const string SP_USER_GETALLUSERS = "IssueTracker_User_GetAllUsers";
        private const string SP_USER_GETALLUSERSBYROLENAME = "IssueTracker_User_GetAllUsersByRoleName";
        private const string SP_USER_GETUSERBYID = "IssueTracker_User_GetUserById";
        private const string SP_USER_GETUSERBYUSERNAME = "IssueTracker_User_GetUserByUsername";
        private const string SP_USER_GETUSERSBYPROJECTID = "IssueTracker_User_GetUsersByProjectId";
        private const string SP_USER_UPDATEUSER = "IssueTracker_User_UpdateUser";
        

        private void AddParamToSQLCmd(SqlCommand sqlCmd, string paramId, SqlDbType sqlType, int paramSize, ParameterDirection paramDirection, object paramvalue)
        {
            if (sqlCmd == null)
            {
                throw new ArgumentNullException("sqlCmd");
            }
            if (StringType.StrCmp(paramId, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("paramId");
            }
            SqlParameter newSqlParam = new SqlParameter();
            newSqlParam.ParameterName = paramId;
            newSqlParam.SqlDbType = sqlType;
            newSqlParam.Direction = paramDirection;
            if (paramSize > 0)
            {
                newSqlParam.Size = paramSize;
            }
            if (paramvalue != null)
            {
                newSqlParam.Value = RuntimeHelpers.GetObjectValue(paramvalue);
            }
            sqlCmd.Parameters.Add(newSqlParam);
        }

        public override bool AddUserToProject(int userId, int projectId)
        {
            if (userId <= DefaultValues.GetUserIdMinValue())
            {
                throw new ArgumentOutOfRangeException("userId");
            }
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.Int, 0, ParameterDirection.Input, userId);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_AddUserToProject");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool Authenticate(string username, string password)
        {
            if ((username == null) || (username.Length == 0))
            {
                throw new ArgumentOutOfRangeException("username");
            }
            if ((password == null) || (password.Length == 0))
            {
                throw new ArgumentOutOfRangeException("password");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NText, 0xff, ParameterDirection.Input, username);
            this.AddParamToSQLCmd(sqlCmd, "@Password", SqlDbType.NText, 0xff, ParameterDirection.Input, password);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_User_Authenticate");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool CloneProject(int projectId, string projectName)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NVarChar, 0xff, ParameterDirection.Input, projectName);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_CloneProject");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override int CreateNewCategory(Category newCategory)
        {
            if (newCategory == null)
            {
                throw new ArgumentNullException("newCategory");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 0x100, ParameterDirection.Input, newCategory.Name);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 4, ParameterDirection.Input, newCategory.ProjectId);
            this.AddParamToSQLCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 4, ParameterDirection.Input, newCategory.ParentCategoryId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectCategories_CreateNewCategory");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewChildIssue(int primaryIssueId, int secondaryIssueId)
        {
            if (primaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("primaryIssueId");
            }
            if (secondaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("secondaryIssueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@primaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, primaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@secondaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, secondaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@relationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.ParentChild);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_CreateNewChildIssue");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewCustomField(CustomField newCustomField)
        {
            if (newCustomField == null)
            {
                throw new ArgumentNullException("newCustomField");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newCustomField.ProjectId);
            this.AddParamToSQLCmd(sqlCmd, "@CustomFieldName", SqlDbType.NText, 50, ParameterDirection.Input, newCustomField.Name);
            this.AddParamToSQLCmd(sqlCmd, "@CustomFieldDataType", SqlDbType.Int, 0, ParameterDirection.Input, newCustomField.DataType);
            this.AddParamToSQLCmd(sqlCmd, "@CustomFieldRequired", SqlDbType.Bit, 0, ParameterDirection.Input, newCustomField.Required);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_CustomField_CreateNewCustomField");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int[] CreateNewIssue(Issue newIssue)
        {
            if (newIssue == null)
            {
                throw new ArgumentNullException("newIssue");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@HistoryId", SqlDbType.Int, 0, ParameterDirection.Output, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newIssue.ProjectId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueTitle", SqlDbType.NText, 0xff, ParameterDirection.Input, newIssue.Title);
            this.AddParamToSQLCmd(sqlCmd, "@IssueCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newIssue.CategoryId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueStatusId", SqlDbType.Int, 0, ParameterDirection.Input, newIssue.StatusId);
            this.AddParamToSQLCmd(sqlCmd, "@IssuePriorityId", SqlDbType.Int, 0, ParameterDirection.Input, newIssue.PriorityId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueMilestoneId", SqlDbType.Int, 0, ParameterDirection.Input, newIssue.MilestoneId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueAssignedId", SqlDbType.Int, 0, ParameterDirection.Input, newIssue.AssignedId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueOwnerId", SqlDbType.Int, 0, ParameterDirection.Input, newIssue.OwnerId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueCreatorUsername", SqlDbType.NText, 0xff, ParameterDirection.Input, newIssue.CreatorUsername);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_CreateNewIssue");
            this.ExecuteScalarCmd(sqlCmd);
            int[] returnvalue = new int[2];
            returnvalue[0] = Convert.ToInt32(sqlCmd.Parameters["@HistoryId"].Value);
            returnvalue[1] = IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
            return returnvalue;
        }

        public override int CreateNewIssueAttachment(IssueAttachment newAttachment)
        {
            if (newAttachment == null)
            {
                throw new ArgumentNullException("newAttachment");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, newAttachment.IssueId);
            this.AddParamToSQLCmd(sqlCmd, "@CreatorUserName", SqlDbType.NText, 0x100, ParameterDirection.Input, newAttachment.CreatorUsername);
            this.AddParamToSQLCmd(sqlCmd, "@FileName", SqlDbType.NText, 250, ParameterDirection.Input, newAttachment.FileName);
            this.AddParamToSQLCmd(sqlCmd, "@ContentType", SqlDbType.NText, 50, ParameterDirection.Input, newAttachment.ContentType);
            sqlCmd.Parameters.Add("@Attachment", SqlDbType.Image, newAttachment.Attachment.Length);
            sqlCmd.Parameters["@Attachment"].Value = newAttachment.Attachment;
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueAttachment_CreateNewIssueAttachment");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewIssueComment(IssueComment newComment)
        {
            if (newComment == null)
            {
                throw new ArgumentNullException("newComment");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, newComment.IssueId);
            this.AddParamToSQLCmd(sqlCmd, "@CreatorUserName", SqlDbType.NText, 0x100, ParameterDirection.Input, newComment.CreatorUsername);
            sqlCmd.Parameters.Add("@Comment", SqlDbType.NText);
            sqlCmd.Parameters["@Comment"].Value = newComment.Comment;
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueComment_CreateNewIssueComment");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewIssueNotification(IssueNotification newNotification)
        {
            if (newNotification == null)
            {
                throw new ArgumentNullException("newNotification");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, newNotification.IssueId);
            this.AddParamToSQLCmd(sqlCmd, "@NotificationUserName", SqlDbType.NText, 0xff, ParameterDirection.Input, newNotification.NotificationUsername);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueNotification_CreateNewIssueNotification");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewMilestone(Milestone newMileStone)
        {
            if (newMileStone == null)
            {
                throw new ArgumentNullException("newMileStone");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newMileStone.ProjectId);
            this.AddParamToSQLCmd(sqlCmd, "@MilestoneName", SqlDbType.NText, 50, ParameterDirection.Input, newMileStone.Name);
            this.AddParamToSQLCmd(sqlCmd, "@MilestoneImageUrl", SqlDbType.NText, 0xff, ParameterDirection.Input, newMileStone.ImageUrl);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectMilestones_CreateNewMilestone");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewParentIssue(int primaryIssueId, int secondaryIssueId)
        {
            if (primaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("primaryIssueId");
            }
            if (secondaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("secondaryIssueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@primaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, primaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@secondaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, secondaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@relationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.ParentChild);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_CreateNewParentIssue");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewPriority(Priority newPriority)
        {
            if (newPriority == null)
            {
                throw new ArgumentNullException("newPriority");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newPriority.ProjectId);
            this.AddParamToSQLCmd(sqlCmd, "@PriorityName", SqlDbType.NText, 50, ParameterDirection.Input, newPriority.Name);
            this.AddParamToSQLCmd(sqlCmd, "@PriorityImageUrl", SqlDbType.NText, 0xff, ParameterDirection.Input, newPriority.ImageUrl);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectPriorities_CreateNewPriority");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewProject(Project newProject)
        {
            if (newProject == null)
            {
                throw new ArgumentNullException("newProject");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NText, 0xff, ParameterDirection.Input, newProject.Name);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectDescription", SqlDbType.NText, 0x3e8, ParameterDirection.Input, newProject.Description);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectManagerId", SqlDbType.Int, 0, ParameterDirection.Input, newProject.ManagerId);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectCreatorUserName", SqlDbType.NText, 0xff, ParameterDirection.Input, newProject.CreatorUserName);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_CreateNewProject");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewRelatedIssue(int primaryIssueId, int secondaryIssueId)
        {
            if (primaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("primaryIssueId");
            }
            if (secondaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("secondaryIssueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@primaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, primaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@secondaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, secondaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@relationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.Related);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_CreateNewRelatedIssue");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewStatus(Status newStatus)
        {
            if (newStatus == null)
            {
                throw new ArgumentNullException("newStatus");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newStatus.ProjectId);
            this.AddParamToSQLCmd(sqlCmd, "@StatusName", SqlDbType.NText, 50, ParameterDirection.Input, newStatus.Name);
            this.AddParamToSQLCmd(sqlCmd, "@StatusImageUrl", SqlDbType.NText, 0xff, ParameterDirection.Input, newStatus.ImageUrl);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectStatus_CreateNewStatus");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override int CreateNewUser(ITUser newUser)
        {
            if (newUser == null)
            {
                throw new ArgumentNullException("newUser");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NText, 0x100, ParameterDirection.Input, newUser.Username);
            this.AddParamToSQLCmd(sqlCmd, "@RoleName", SqlDbType.NText, 0x100, ParameterDirection.Input, newUser.RoleName);
            this.AddParamToSQLCmd(sqlCmd, "@Email", SqlDbType.NText, 0x100, ParameterDirection.Input, newUser.Email);
            this.AddParamToSQLCmd(sqlCmd, "@DisplayName", SqlDbType.NText, 0x100, ParameterDirection.Input, newUser.DisplayName);
            this.AddParamToSQLCmd(sqlCmd, "@UserPassword", SqlDbType.NText, 0x100, ParameterDirection.Input, newUser.Password);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_User_CreateNewUser");
            this.ExecuteScalarCmd(sqlCmd);
            return IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value);
        }

        public override bool DeleteCategory(int categoryId)
        {
            if (categoryId <= DefaultValues.GetCategoryIdMinValue())
            {
                throw new ArgumentOutOfRangeException("categoryId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@CategoryIdToDelete", SqlDbType.Int, 4, ParameterDirection.Input, categoryId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectCategories_DeleteCategory");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool DeleteChildIssue(int primaryIssueId, int secondaryIssueId)
        {
            if (primaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("primaryIssueId");
            }
            if (secondaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("secondaryIssueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@primaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, primaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@secondaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, secondaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@relationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.ParentChild);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_DeleteChildIssue");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool DeleteCustomField(int customFieldId)
        {
            if (customFieldId <= DefaultValues.GetCustomFieldIdMinValue())
            {
                throw new ArgumentOutOfRangeException("customFieldId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@CustomIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, customFieldId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_CustomField_DeleteCustomField");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool DeleteIssue(int issueID)
        {
            if (issueID <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueID");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@IssueIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, issueID);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_DeleteIssue");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override void DeleteIssueAttachment(int attachmentId)
        {
            if (attachmentId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("attachmentId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@AttachmentId", SqlDbType.Int, 0, ParameterDirection.Input, attachmentId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueAttachment_Delete");
            this.ExecuteScalarCmd(sqlCmd);
        }

        public override bool DeleteIssueNotification(int issueId, string username)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            if (StringType.StrCmp(username, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("username");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NVarChar, 0xff, ParameterDirection.Input, username);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueNotification_DeleteIssueNotification");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool DeleteMilestone(int milestoneId)
        {
            if (milestoneId <= DefaultValues.GetMilestoneIdMinValue())
            {
                throw new ArgumentOutOfRangeException("milestoneId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@MilestoneIdToDelete", SqlDbType.Int, 4, ParameterDirection.Input, milestoneId);
            this.AddParamToSQLCmd(sqlCmd, "@ResultValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectMilestones_DeleteMilestone");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ResultValue"].Value) == 0, true, false));
        }

        public override bool DeleteParentIssue(int primaryIssueId, int secondaryIssueId)
        {
            if (primaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("primaryIssueId");
            }
            if (secondaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("secondaryIssueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@primaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, primaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@secondaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, secondaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@relationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.ParentChild);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_DeleteParentIssue");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool DeletePriority(int PriorityId)
        {
            if (PriorityId <= DefaultValues.GetPriorityIdMinValue())
            {
                throw new ArgumentOutOfRangeException("PriorityId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@PriorityIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, PriorityId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectPriorities_DeletePriority");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool DeleteProject(int projectID)
        {
            if (projectID <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectID");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, projectID);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_DeleteProject");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool DeleteQuery(int queryId)
        {
            if (queryId <= DefaultValues.GetQueryIdMinValue())
            {
                throw new ArgumentOutOfRangeException("queryId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@queryId", SqlDbType.Int, 0, ParameterDirection.Input, queryId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Query_DeleteQuery");
            this.ExecuteScalarCmd(sqlCmd);
            return true;
        }

        public override bool DeleteRelatedIssue(int primaryIssueId, int secondaryIssueId)
        {
            if (primaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("primaryIssueId");
            }
            if (secondaryIssueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("secondaryIssueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@PrimaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, primaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@SecondaryIssueId", SqlDbType.Int, 0, ParameterDirection.Input, secondaryIssueId);
            this.AddParamToSQLCmd(sqlCmd, "@relationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.Related);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_DeleteRelatedIssue");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool DeleteStatus(int statusId)
        {
            if (statusId <= DefaultValues.GetStatusIdMinValue())
            {
                throw new ArgumentOutOfRangeException("statusId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@StatusIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, statusId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectStatus_DeleteStatus");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        private CollectionBase ExecuteReaderCmd(SqlCommand sqlCmd, DataAccessLayerBaseClass.GenerateCollectionFromReader gcfr)
        {
            if (StringType.StrCmp(this.ConnectionString, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("ConnectionString");
            }
            if (sqlCmd == null)
            {
                throw new ArgumentNullException("sqlCmd");
            }
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                return gcfr(sqlCmd.ExecuteReader());
            }
        }

        private object ExecuteScalarCmd(SqlCommand sqlCmd)
        {
            if (StringType.StrCmp(this.ConnectionString, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("ConnectionString");
            }
            if (sqlCmd == null)
            {
                throw new ArgumentNullException("sqlCmd");
            }
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                return RuntimeHelpers.GetObjectValue(sqlCmd.ExecuteScalar());
            }
        }

        public override ProjectCollection GetAllProjects()
        {
            SqlCommand sqlCmd = new SqlCommand();
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_GetAllProjects");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateProjectCollectionFromReader);
            return (ProjectCollection)this.ExecuteReaderCmd(sqlCmd, test);
        }

        public override RoleCollection GetAllRoles()
        {
            SqlCommand sqlCmd = new SqlCommand();
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Role_GetAllRoles");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateRoleCollectionFromReader);
            return (RoleCollection)this.ExecuteReaderCmd(sqlCmd, test);
        }

        public override ITUserCollection GetAllUsers()
        {
            SqlCommand sqlCmd = new SqlCommand();
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_User_GetAllUsers");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateITUserCollectionFromReader);
            return (ITUserCollection)this.ExecuteReaderCmd(sqlCmd, test);
        }

        public override ITUserCollection GetAllUsersByRoleName(string roleName)
        {
            if ((roleName == null) || (roleName.Length == 0))
            {
                throw new ArgumentOutOfRangeException("password");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.NText, 0, ParameterDirection.Input, roleName);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_User_GetAllUsersByRoleName");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateITUserCollectionFromReader);
            return (ITUserCollection)this.ExecuteReaderCmd(sqlCmd, test);
        }

        public override CategoryCollection GetCategoriesByProjectId(int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectCategories_GetCategoriesByProjectId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateCategoryCollectionFromReader);
            return (CategoryCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override RelatedIssueCollection GetChildIssues(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.AddParamToSQLCmd(sqlCmd, "@RelationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.ParentChild);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_GetChildIssues");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateRelatedIssueCollectionFromReader);
            return (RelatedIssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override CustomFieldCollection GetCustomFieldsByIssueId(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@issueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_CustomField_GetCustomFieldsByIssueId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateCustomFieldCollectionFromReader);
            return (CustomFieldCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override CustomFieldCollection GetCustomFieldsByIssueId_2(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@issueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_CustomField_GetCustomFieldsByIssueId_2");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateCustomFieldCollectionFromReader);
            return (CustomFieldCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override CustomFieldCollection GetCustomFieldsByProjectId(int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_CustomField_GetCustomFieldsByProjectId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateCustomFieldCollectionFromReader);
            return (CustomFieldCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueAttachment GetIssueAttachmentById(int attachmentId)
        {
            IssueAttachment attachment = null;
            if (attachmentId <= DefaultValues.GetIssueAttachmentIdMinValue())
            {
                throw new ArgumentOutOfRangeException("attachmentId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@AttachmentId", SqlDbType.Int, 0, ParameterDirection.Input, attachmentId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueAttachment_GetIssueAttachmentById");
            if (StringType.StrCmp(this.ConnectionString, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("ConnectionString");
            }
            SqlConnection cn = new SqlConnection(this.ConnectionString);
            try
            {
                sqlCmd.Connection = cn;
                cn.Open();
                SqlDataReader dtr = sqlCmd.ExecuteReader();
                if (dtr.Read())
                {
                    attachment = new IssueAttachment(StringType.FromObject(dtr["FileName"]), StringType.FromObject(dtr["ContentType"]), (byte[])dtr["Attachment"]);
                }
            }
            finally
            {
                cn.Close();
            }
            return attachment;
        }

        public override IssueAttachmentCollection GetIssueAttachmentsByIssueId(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueCommentIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueAttachment_GetIssueAttachmentsByIssueId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueAttachmentCollectionFromReader);
            return (IssueAttachmentCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override Issue GetIssueById(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_GetIssueById");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueCollectionFromReader);
            IssueCollection issCollection = (IssueCollection)this.ExecuteReaderCmd(sqlCmd, test);
            if (issCollection.Count > 0)
            {
                return issCollection[0];
            }
            return null;
        }

        public override IssueCommentCollection GetIssueCommentsByIssueId(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueCommentIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueComment_GetIssueCommentsByIssueId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueCommentCollectionFromReader);
            return (IssueCommentCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueHistoryCollection GetIssueHistoryByIssueId(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueHistory_GetIssueHistoryByIssueId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueHistoryCollectionFromReader);
            return (IssueHistoryCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueNotificationCollection GetIssueNotificationsByIssueId(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueNotification_GetIssueNotificationsByIssueId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueNotificationCollectionFromReader);
            return (IssueNotificationCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueCollection GetIssuesByAssignedUsername(int projectId, string assignedUsername)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            if ((assignedUsername == null) || (StringType.StrCmp(assignedUsername, string.Empty, false) == 0))
            {
                throw new ArgumentOutOfRangeException("assignedUsername");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NVarChar, 0xff, ParameterDirection.Input, assignedUsername);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_GetIssuesByAssignedUsername");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueCollectionFromReader);
            return (IssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueCollection GetIssuesByCreatorUsername(int projectId, string creatorUsername)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            if (StringType.StrCmp(creatorUsername, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("creatorUsername");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@projectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.AddParamToSQLCmd(sqlCmd, "@creatorUsername", SqlDbType.NText, 0xff, ParameterDirection.Input, creatorUsername);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_GetIssuesByCreatorUsername");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueCollectionFromReader);
            return (IssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueCollection GetIssuesByOwnerUsername(int projectId, string ownerUsername)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            if (StringType.StrCmp(ownerUsername, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("ownerUsername");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NText, 0xff, ParameterDirection.Input, ownerUsername);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_GetIssuesByOwnerUsername");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueCollectionFromReader);
            return (IssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueCollection GetIssuesByProjectId(int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_GetIssuesByProjectId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueCollectionFromReader);
            return (IssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueCollection GetIssuesByRelevancy(int projectId, string username)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            if (StringType.StrCmp(username, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("username");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@projectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.AddParamToSQLCmd(sqlCmd, "@username", SqlDbType.NText, 0xff, ParameterDirection.Input, username);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_GetIssuesByRelevancy");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueCollectionFromReader);
            return (IssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override MilestoneCollection GetMilestoneByProjectId(int projectId)
        {
            if (projectId <= DefaultValues.GetMilestoneIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectMilestones_GetMilestonesByProjectId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateMilestoneCollectionFromReader);
            return (MilestoneCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override RelatedIssueCollection GetParentIssues(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.AddParamToSQLCmd(sqlCmd, "@RelationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.ParentChild);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_GetParentIssues");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateRelatedIssueCollectionFromReader);
            return (RelatedIssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override PriorityCollection GetPrioritiesByProjectId(int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectPriorities_GetPrioritiesByProjectId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GeneratePriorityCollectionFromReader);
            return (PriorityCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override Project GetProjectById(int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_GetProjectById");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateProjectCollectionFromReader);
            ProjectCollection prjCollection = (ProjectCollection)this.ExecuteReaderCmd(sqlCmd, test);
            if (prjCollection.Count > 0)
            {
                return prjCollection[0];
            }
            return null;
        }

        public override ProjectCollection GetProjectByMemberUsername(string username)
        {
            if ((username == null) || (StringType.StrCmp(username, string.Empty, false) == 0))
            {
                throw new ArgumentOutOfRangeException("username");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NText, 0xff, ParameterDirection.Input, username);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_GetProjectByUsername");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateProjectCollectionFromReader);
            return (ProjectCollection)this.ExecuteReaderCmd(sqlCmd, test);
        }

        public override QueryCollection GetQueriesByUsername(string username, int projectId)
        {
            if ((username == null) || (StringType.StrCmp(username, string.Empty, false) == 0))
            {
                throw new ArgumentOutOfRangeException("username");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NVarChar, 0xff, ParameterDirection.Input, username);
            this.AddParamToSQLCmd(sqlCmd, "@projectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Query_GetQueriesByUsername");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateQueryCollectionFromReader);
            return (QueryCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override RelatedIssueCollection GetRelatedIssues(int issueId)
        {
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueId);
            this.AddParamToSQLCmd(sqlCmd, "@RelationType", SqlDbType.Int, 0, ParameterDirection.Input, IssueRelationType.Related);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_RelatedIssue_GetRelatedIssues");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateRelatedIssueCollectionFromReader);
            return (RelatedIssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override StatusCollection GetStatusByProjectId(int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_ProjectStatus_GetStatusByProjectId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateStatusCollectionFromReader);
            return (StatusCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override ITUser GetUserById(int userId)
        {
            if (userId <= DefaultValues.GetUserIdMinValue())
            {
                throw new ArgumentOutOfRangeException("userId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.Int, 0, ParameterDirection.Input, userId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_User_GetUserById");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateITUserCollectionFromReader);
            ITUserCollection userCollection = (ITUserCollection)this.ExecuteReaderCmd(sqlCmd, test);
            if (userCollection.Count > 0)
            {
                return userCollection[0];
            }
            return null;
        }

        public override ITUser GetUserByUsername(string username)
        {
            if ((username == null) || (username.Length == 0))
            {
                throw new ArgumentOutOfRangeException("username");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NText, 0xff, ParameterDirection.Input, username);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_User_GetUserByUsername");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateITUserCollectionFromReader);
            ITUserCollection userCollection = (ITUserCollection)this.ExecuteReaderCmd(sqlCmd, test);
            if (userCollection.Count > 0)
            {
                return userCollection[0];
            }
            return null;
        }

        public override ITUserCollection GetUsersByProjectId(int projectId)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_User_GetUsersByProjectId");
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateITUserCollectionFromReader);
            return (ITUserCollection)this.ExecuteReaderCmd(sqlCmd, test);
        }

        public override IssueCollection PerformQuery(int projectId, QueryClauseCollection queryClauses)
        {
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            if (queryClauses.Count == 0)
            {
                throw new ArgumentOutOfRangeException("queryClauses");
            }
            StringBuilder commandBuilder = new StringBuilder();
            commandBuilder.Append("SELECT * FROM IssueTracker_IssuesView WHERE ProjectId=@projectId AND Disabled=0 AND IssueId IN (SELECT IssueId FROM IssueTracker_Issues WHERE 1=1 ");
            int i = 0;
            foreach (QueryClause qc in queryClauses)
            {
                commandBuilder.AppendFormat(" {0} {1} {2} @p{3}", new object[] { qc.BooleanOperator, qc.FieldName, qc.ComparisonOperator, i });
                i++;
            }
            commandBuilder.Append(") ORDER BY IssueId DESC");
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandText = commandBuilder.ToString();
            sqlCmd.Parameters.Add("@projectId", SqlDbType.Int).Value = projectId;
            i = 0;
            foreach (QueryClause qc in queryClauses)
            {
                sqlCmd.Parameters.Add("@p" + i.ToString(), qc.DataType).Value = qc.FieldValue;
                i++;
            }
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateIssueCollectionFromReader);
            return (IssueCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
        }

        public override IssueCollection PerformSavedQuery(int projectId, int queryId)
        {
            if (queryId <= DefaultValues.GetQueryIdMinValue())
            {
                throw new ArgumentOutOfRangeException("queryId");
            }
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@QueryId", SqlDbType.Int, 0, ParameterDirection.Input, queryId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Query_GetSavedQuery");
            DataAccessLayerBaseClass.GenerateCollectionFromReader sqlData = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateQueryClauseCollectionFromReader);
            QueryClauseCollection queryClauses = (QueryClauseCollection)this.ExecuteReaderCmd(sqlCmd, sqlData);
            return this.PerformQuery(projectId, queryClauses);
        }

        public override bool RemoveUserFromProject(int userId, int projectId)
        {
            if (userId <= DefaultValues.GetUserIdMinValue())
            {
                throw new ArgumentOutOfRangeException("userId");
            }
            if (projectId <= DefaultValues.GetProjectIdMinValue())
            {
                throw new ArgumentOutOfRangeException("projectId");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.Int, 0, ParameterDirection.Input, userId);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_RemoveUserFromProject");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool SaveCustomFieldValues(int issueId, int historyid, CustomFieldCollection fields)
        {
            if (fields == null)
            {
                throw new ArgumentNullException("fields");
            }
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            if (StringType.StrCmp(this.ConnectionString, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("ConnectionString");
            }
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
            sqlCmd.Parameters.Add("@IssueId", SqlDbType.Int, 0).Direction = ParameterDirection.Input;
            sqlCmd.Parameters.Add("@HistoryId", SqlDbType.Int, 0).Direction = ParameterDirection.Input;
            sqlCmd.Parameters.Add("@CustomFieldId", SqlDbType.Int, 0).Direction = ParameterDirection.Input;
            sqlCmd.Parameters.Add("@CustomFieldValue", SqlDbType.NVarChar, 0xff).Direction = ParameterDirection.Input;
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_CustomField_SaveCustomFieldValue");
            bool errors = false;
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                IEnumerator _Vb_t_ref_0 = null;
                sqlCmd.Connection = cn;
                cn.Open();
                try
                {
                    _Vb_t_ref_0 = fields.GetEnumerator();
                    while (_Vb_t_ref_0.MoveNext())
                    {
                        CustomField field = (CustomField)_Vb_t_ref_0.Current;
                        sqlCmd.Parameters["@IssueId"].Value = issueId;
                        sqlCmd.Parameters["@HistoryId"].Value = historyid;
                        sqlCmd.Parameters["@CustomFieldId"].Value = field.Id;
                        sqlCmd.Parameters["@CustomFieldValue"].Value = field.Value;
                        sqlCmd.ExecuteScalar();
                        if (IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 1)
                        {
                            errors = true;
                        }
                    }
                }
                finally
                {
                    if (_Vb_t_ref_0 is IDisposable)
                    {
                        ((IDisposable)_Vb_t_ref_0).Dispose();
                    }
                }
            }
            return !errors;
        }

        public override bool SaveQuery(string username, int projectId, string queryName, QueryClauseCollection queryClauses)
        {
            if ((username == null) || (StringType.StrCmp(username, string.Empty, false) == 0))
            {
                throw new ArgumentOutOfRangeException("username");
            }
            if ((queryName == null) || (StringType.StrCmp(queryName, string.Empty, false) == 0))
            {
                throw new ArgumentOutOfRangeException("queryName");
            }
            if (queryClauses.Count == 0)
            {
                throw new ArgumentOutOfRangeException("queryClauses");
            }
            SqlCommand cmdSaveQuery = new SqlCommand();
            this.AddParamToSQLCmd(cmdSaveQuery, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(cmdSaveQuery, "@Username", SqlDbType.NVarChar, 0xff, ParameterDirection.Input, username);
            this.AddParamToSQLCmd(cmdSaveQuery, "@projectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            this.AddParamToSQLCmd(cmdSaveQuery, "@QueryName", SqlDbType.NText, 50, ParameterDirection.Input, queryName);
            this.SetCommandType(cmdSaveQuery, CommandType.StoredProcedure, "IssueTracker_Query_SaveQuery");
            SqlCommand cmdClause = new SqlCommand();
            cmdClause.Parameters.Add("@QueryId", SqlDbType.Int);
            cmdClause.Parameters.Add("@BooleanOperator", SqlDbType.NVarChar, 50);
            cmdClause.Parameters.Add("@FieldName", SqlDbType.NVarChar, 50);
            cmdClause.Parameters.Add("@ComparisonOperator", SqlDbType.NVarChar, 50);
            cmdClause.Parameters.Add("@FieldValue", SqlDbType.NVarChar, 50);
            cmdClause.Parameters.Add("@DataType", SqlDbType.Int);
            this.SetCommandType(cmdClause, CommandType.StoredProcedure, "IssueTracker_Query_SaveQueryClause");
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                IEnumerator _Vb_t_ref_0 = null;
                cn.Open();
                cmdSaveQuery.Connection = cn;
                cmdSaveQuery.ExecuteNonQuery();
                int queryId = IntegerType.FromObject(cmdSaveQuery.Parameters["@ReturnValue"].Value);
                if (queryId == 0)
                {
                    return false;
                }
                cmdClause.Connection = cn;
                try
                {
                    _Vb_t_ref_0 = queryClauses.GetEnumerator();
                    while (_Vb_t_ref_0.MoveNext())
                    {
                        QueryClause clause = (QueryClause)_Vb_t_ref_0.Current;
                        cmdClause.Parameters["@QueryId"].Value = queryId;
                        cmdClause.Parameters["@BooleanOperator"].Value = clause.BooleanOperator;
                        cmdClause.Parameters["@FieldName"].Value = clause.FieldName;
                        cmdClause.Parameters["@ComparisonOperator"].Value = clause.ComparisonOperator;
                        cmdClause.Parameters["@FieldValue"].Value = clause.FieldValue;
                        cmdClause.Parameters["@DataType"].Value = clause.DataType;
                        cmdClause.ExecuteNonQuery();
                    }
                }
                finally
                {
                    if (_Vb_t_ref_0 is IDisposable)
                    {
                        ((IDisposable)_Vb_t_ref_0).Dispose();
                    }
                }
            }
            return true;
        }

        public override bool SaveSingleCustomFieldValues(int issueId, string field)
        {
            if (StringType.StrCmp(field, "", false) == 0)
            {
                throw new ArgumentNullException("field");
            }
            if (issueId <= DefaultValues.GetIssueIdMinValue())
            {
                throw new ArgumentOutOfRangeException("issueId");
            }
            if (StringType.StrCmp(this.ConnectionString, string.Empty, false) == 0)
            {
                throw new ArgumentOutOfRangeException("ConnectionString");
            }
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
            sqlCmd.Parameters.Add("@IssueId", SqlDbType.Int, 0).Direction = ParameterDirection.Input;
            sqlCmd.Parameters.Add("@CustomFieldId", SqlDbType.Int, 0).Direction = ParameterDirection.Input;
            sqlCmd.Parameters.Add("@CustomFieldValue", SqlDbType.NVarChar, 0xff).Direction = ParameterDirection.Input;
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_CustomField_SaveCustomFieldValue");
            bool errors = false;
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                CustomField cus = null;
                sqlCmd.Connection = cn;
                cn.Open();
                sqlCmd.Parameters["@IssueId"].Value = issueId;
                sqlCmd.Parameters["@CustomFieldId"].Value = cus.Id;
                sqlCmd.Parameters["@CustomFieldValue"].Value = cus.Value;
                sqlCmd.ExecuteScalar();
                if (IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 1)
                {
                    errors = true;
                }
            }
            return !errors;
        }

        private void SetCommandType(SqlCommand sqlCmd, CommandType cmdType, string cmdText)
        {
            sqlCmd.CommandType = cmdType;
            sqlCmd.CommandText = cmdText;
        }

        public override bool UpdateCustomField(CustomField customFieldToUpdate)
        {
            if (customFieldToUpdate == null)
            {
                throw new ArgumentNullException("customFieldToUpdate");
            }
            if (customFieldToUpdate.Id <= 0)
            {
                throw new ArgumentOutOfRangeException("customFieldToUpdate.Id");
            }
            if ((customFieldToUpdate.Name == null) || (customFieldToUpdate.Name.Length == 0))
            {
                throw new ArgumentOutOfRangeException("customFieldToUpdate.Name");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ResultValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@CustomFieldId", SqlDbType.Int, 0, ParameterDirection.Input, customFieldToUpdate.Id);
            this.AddParamToSQLCmd(sqlCmd, "@CustomFieldName", SqlDbType.NText, 50, ParameterDirection.Input, customFieldToUpdate.Name);
            this.AddParamToSQLCmd(sqlCmd, "@CustomFieldDataType", SqlDbType.Int, 0, ParameterDirection.Input, customFieldToUpdate.DataType);
            this.AddParamToSQLCmd(sqlCmd, "@CustomFieldRequired", SqlDbType.Bit, 0, ParameterDirection.Input, customFieldToUpdate.Required);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_CustomField_UpdateCustomField");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override int UpdateIssue(Issue issueToUpdate)
        {
            if (issueToUpdate == null)
            {
                throw new ArgumentNullException("issueToUpdate");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@IssueId", SqlDbType.Int, 0, ParameterDirection.Input, issueToUpdate.Id);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, issueToUpdate.ProjectId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueTitle", SqlDbType.NText, 0xff, ParameterDirection.Input, issueToUpdate.Title);
            this.AddParamToSQLCmd(sqlCmd, "@IssueCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, issueToUpdate.CategoryId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueStatusId", SqlDbType.Int, 0, ParameterDirection.Input, issueToUpdate.StatusId);
            this.AddParamToSQLCmd(sqlCmd, "@IssuePriorityId", SqlDbType.Int, 0, ParameterDirection.Input, issueToUpdate.PriorityId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueMilestoneId", SqlDbType.Int, 0, ParameterDirection.Input, issueToUpdate.MilestoneId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueAssignedId", SqlDbType.Int, 0, ParameterDirection.Input, issueToUpdate.AssignedId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueOwnerId", SqlDbType.Int, 0, ParameterDirection.Input, issueToUpdate.OwnerId);
            this.AddParamToSQLCmd(sqlCmd, "@IssueCreatorUsername", SqlDbType.NText, 0xff, ParameterDirection.Input, issueToUpdate.CreatorUsername);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Issue_UpdateIssue");
            this.ExecuteScalarCmd(sqlCmd);
            int historyId = Convert.ToInt32(sqlCmd.Parameters["@ReturnValue"].Value);
            return historyId >= 0 ? historyId : -1;
        }

        public override bool UpdateProject(Project projectToUpdate)
        {
            if (projectToUpdate == null)
            {
                throw new ArgumentNullException("projectToUpdate");
            }
            if (projectToUpdate.Id <= 0)
            {
                throw new ArgumentOutOfRangeException("projectToUpdate");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectToUpdate.Id);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NText, 0x100, ParameterDirection.Input, projectToUpdate.Name);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectDescription", SqlDbType.NText, 0x3e8, ParameterDirection.Input, projectToUpdate.Description);
            this.AddParamToSQLCmd(sqlCmd, "@ProjectManagerId", SqlDbType.Int, 0, ParameterDirection.Input, projectToUpdate.ManagerId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_Project_UpdateProject");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override bool UpdateUser(ITUser userToUpdate)
        {
            if (userToUpdate == null)
            {
                throw new ArgumentNullException("userToUpdate");
            }
            if (userToUpdate.Id <= 0)
            {
                throw new ArgumentOutOfRangeException("userToUpdate");
            }
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            this.AddParamToSQLCmd(sqlCmd, "@UserId", SqlDbType.Int, 0, ParameterDirection.Input, userToUpdate.Id);
            this.AddParamToSQLCmd(sqlCmd, "@Username", SqlDbType.NText, 0x100, ParameterDirection.Input, userToUpdate.Username);
            this.AddParamToSQLCmd(sqlCmd, "@RoleName", SqlDbType.NText, 0x100, ParameterDirection.Input, userToUpdate.RoleName);
            this.AddParamToSQLCmd(sqlCmd, "@Email", SqlDbType.NText, 0x100, ParameterDirection.Input, userToUpdate.Email);
            this.AddParamToSQLCmd(sqlCmd, "@DisplayName", SqlDbType.NText, 0x100, ParameterDirection.Input, userToUpdate.DisplayName);
            this.AddParamToSQLCmd(sqlCmd, "@UserPassword", SqlDbType.NText, 0x100, ParameterDirection.Input, userToUpdate.Password);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_User_UpdateUser");
            this.ExecuteScalarCmd(sqlCmd);
            return BooleanType.FromObject(Interaction.IIf(IntegerType.FromObject(sqlCmd.Parameters["@ReturnValue"].Value) == 0, true, false));
        }

        public override IssueHistoryValueCollection GetHistoryValueByHistoryId(int HistoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            this.AddParamToSQLCmd(sqlCmd, "@HistoryId", SqlDbType.Int, 0, ParameterDirection.Input, HistoryId);
            this.SetCommandType(sqlCmd, CommandType.StoredProcedure, "IssueTracker_IssueHistory_GetHistoryValueByHistoryId");
            DataAccessLayerBaseClass.GenerateCollectionFromReader test = new DataAccessLayerBaseClass.GenerateCollectionFromReader(this.GenerateRoleCollectionFromReader);
            return (IssueHistoryValueCollection)this.ExecuteReaderCmd(sqlCmd, test);
        }

        public override bool SupportsProjectCloning
        {
            get
            {
                return true;
            }
        }
    }
}

